SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE   FUNCTION [live].[ValcomBase] (@ExportId INT , @FundId VARCHAR(50))
RETURNS @results TABLE 
(
  
			  PortfolioID VARCHAR(50) ,
			  PortfolioName VARCHAR(50) ,
			  InstrClass VARCHAR(100) ,
			  InstrName VARCHAR(200) ,
			  IssuerName VARCHAR(200) ,
			  OrigPurchDate DATETIME ,
			  IsQualified VARCHAR(2) ,
			  AssetQualityRating INT ,
			  PricingDepth VARCHAR(10) ,
			  EstimateDuration DECIMAL(38, 12) ,
			  Moody_Rating VARCHAR(50) ,
			  Portfolio_Moody_Rating VARCHAR(50) ,
			  Fund_Moody_Rating VARCHAR(50) ,
			  GAAP_Pur VARCHAR(50) ,
			  GAAP_Cur VARCHAR(50) ,
			  GAAP_Walk VARCHAR(50) ,
			  Sum_Par DECIMAL(38, 12) ,
			  Amort_Cost_Pct_Par VARCHAR(50) NULL,
			  Weight_Price VARCHAR(50) ,
			  PriorNAV_Price DECIMAL(38, 12) ,
			  NAVDate_Price DECIMAL(38, 12) ,
			  Spread VARCHAR(50) ,
			  Ask DECIMAL(38, 12) ,
			  Bid DECIMAL(38, 12) ,
			  Amort_Cost DECIMAL(38, 12) ,
			  MktVal DECIMAL(38, 12) ,
			  MktVal_Pct DECIMAL(38, 12) ,
			  Net_Unreal_App DECIMAL(38, 12) ,
			  Pct_Net_Unreal_Gain VARCHAR(50) ,
			  DailyVal_Change DECIMAL(38, 12) ,
			  PriceChange_Pct VARCHAR(50) ,
			  SecurityID VARCHAR(50) ,
			  Sum_Unreal_Dep DECIMAL(38, 12) ,
			  Sum_Unreal_App DECIMAL(38, 12) ,
			  Prop_Reason VARCHAR(50) ,
			  Prop_Track VARCHAR(50) ,
			  PricingMethod VARCHAR(50) ,
			  TotalInt_Purch_Price DECIMAL(38, 12) ,
			  TotalInt_Cur_Price DECIMAL(38, 12) ,
			  Sum_OrigCost DECIMAL(38, 12) ,
			  Security_Type VARCHAR(50) ,
			  Issuer_Name VARCHAR(50) ,
			  Lots DECIMAL(38, 12) ,
			  Iss_SubSector VARCHAR(100) ,
			  Iss_Sector VARCHAR(100) ,
			  ExternalID VARCHAR(20) ,
			  FocusGroup VARCHAR(5) ,
			  FocusReason VARCHAR(100) ,
			  portSortOrder INT ,
			  instSortOrder INT ,
			  priorMoodyRating VARCHAR(5) ,
			  InstrumentID INT ,
			  Maturity_Date DATETIME ,
			  Settle_Disc_prch DECIMAL(38, 12) ,
			  Pik_Prch DECIMAL(38, 12) ,
			  Coupon_Purch DECIMAL(38, 12)
			)
 BEGIN 

 INSERT INTO @results
SELECT  
	   a.PortfolioId ,
   MAX(p.PortfolioName)
	---ahInstrClass
	 , CASE WHEN ah.InstrClass = 'Structured Notes' THEN 'Collateralized Securities' ELSE ah.InstrClass END
	 , ah.InstrName
	 , MAX(iss.IssuerName)
	 , MIN(a.PurchDate)
	 , CASE WHEN MAX(CAST(fi.IsQualified AS INT)) = '1' THEN 'Y' ELSE 'N' END
	 , MAX(a.ASSQualrat)
	 , '1'
	 , MAX(a.ESTDuration)
	 , MAX(i.MoodysRating)
	 , MAX(p.MoodysRating)
	 , MAX(f.MoodysRating)
	 , CASE WHEN (MAX(a.GAAPYldPurchPrc)) = 0 THEN 'N/A'
				ELSE CAST(MAX(a.GAAPYldCurrPrc) AS VARCHAR)
		END 
	 , CASE WHEN (MAX(a.GAAPYldCurrPrc)) = 0 THEN 'N/A'
				ELSE CAST(MAX(a.GAAPYldCurrPrc) AS VARCHAR)
		END 
	 , CASE WHEN MAX(i.InstDefId) < 9 OR MAX(i.InstDefId) >11 THEN	 CAST(MAX(a.InvWalkGAAPYld)  AS VARCHAR)
		 ELSE	'N/A'
		 END	 
	 , SUM(a.EndAdjAmt)
	 , CASE WHEN SUM(a.AmortCost) <> '0.0' THEN CAST(SUM(a.AmortCost) / SUM(a.EndAdjAmt) AS              VARCHAR(50)) ELSE 'N/A' END
	 , CASE WHEN SUM(a.EndAdjAmt) <> '0.0' THEN CAST(SUM(a.PurchPrc * a.EndAdjAmt) / SUM(a.EndAdjAmt) AS VARCHAR(50)) ELSE '0.0' END
	 , MAX(a.YesterdayPrice)
	 , MAX(a.TodayPrice)
,Null
,Null
,Null
	 , SUM(a.AmortCost)
	 , SUM(a.MktVal)
	 , SUM(a.MktVal)
	 , SUM(a.UnrealDeprec) + SUM(a.unrealapprec)
	 , CASE WHEN SUM(a.AmortCost) <> '0.0' THEN CAST(( SUM(a.UnrealDeprec) + SUM(a.unrealapprec) ) / SUM(a.AmortCost) AS VARCHAR(50)) ELSE 'N/A' END
	 , SUM(a.DailyValChg)
	 , CASE WHEN MAX(a.YesterdayPrice) > '0.01' AND   MAX(a.TodayPrice) > '0.01' THEN CAST(( ( MAX(a.TodayPrice) / MAX(a.YesterdayPrice) ) - 1 ) AS VARCHAR(50)) ELSE 'N/A' END
	 , MAX(i.extid)
	 , SUM(a.UnrealDeprec)
	 , SUM(a.UnrealApprec)
	 , MAX(ISNULL(ah.PropReason, ''))
	 , MAX(ISNULL(ah.PropTrack, ''))
	 , CASE WHEN MAX(i.PricingMethodType) = 'Cost' THEN 'Cost - to be ' + MAX(i.PricingVendor) ELSE MAX(i.PricingVendor) END
	 , SUM(a.totalIntPurchPrc)
	 , SUM(a.totalintcurrpurchprc)
	 , SUM(a.Cost)
	 , MAX(id.InstTypeDescr)
	 , MAX(iss.IssuerName)
	 , COUNT(ah.InstrName)
	 , MAX(iss.SubSector)
	 , MAX(iss.GICSSector)
	 , MAX(i.ExtId)
,Null
,Null
	 , MAX(p.SortOrder)
	 --ahSortOrder
	 , MAX(ah.InstSortOrder)
	 , MAX(i.MoodysRating)
	 , MAX(i.InstrumentId)
	 , MAX(i.MaturityDate)
	 , SUM(a.SettDiscPurchPrc)
	 , SUM(a.PIKPurchAmt)
	 , SUM(a.CoupRevAnnualPurch)



FROM   [FSFundModel].[Reporting].Asset a
INNER JOIN  ReportingScripts.live.AssetHelper(@ExportId) ah
ON ah.AssetIntrnlId = a.AssetIntrnlId
	INNER JOIN [FSFundModel].[Reporting].Instrument i ON a.ExportId = i.ExportId
											AND a.InstrumentId = i.InstrumentId
	INNER JOIN [FSFundModel].[Reporting].Issuer iss ON a.ExportId = iss.ExportId
											AND i.IssuerId = iss.IssuerId
	INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id ON a.ExportID = id.ExportId
											AND i.InstDefId = id.InstDefId
	INNER JOIN [FSFundModel].[Reporting].FundIssuer fi ON a.ExportID = fi.ExportId
											AND i.IssuerId = fi.IssuerId
	INNER JOIN [FSFundModel].[Reporting].Portfolio p ON a.ExportId = p.ExportId
											AND A.PortfolioId = p.PortfolioId
	INNER JOIN [FSFundModel].[Reporting].Fund f ON a.ExportId = f.ExportId
											AND F.FundId = @FundId	
WHERE   a.ExportId = @ExportId
	AND a.IsActiveEndOfDay = 1
GROUP BY a.PortfolioId ,
	ah.InstrClass ,
	ah.InstrName
--, a.AssetID
	,
	p.SortOrder ,
	ah.InstSortOrder


--INSERT INTO @results 
----------------------------



--  SELECT MAX(fci.PortfolioId)
--	 , MAX(p.PortfolioName) AS portfolioName
--	 , 'Unfunded Positions'
--	 , i.InstrName
--	 , MAX(iss.issuername)
--	 , MIN(a.PurchDate)
--	 , CASE WHEN fi.IsQualified = 1 THEN 'Y' ELSE 'N' END
--	 , MAX(fci.AssQualRat)
--	 , '1'
--	 , MAX(a.EstDuration)
--	 , MAX(i.MoodysRating)
--	 , MAX(f.MoodysRating)
--	 , MAX(f.MoodysRating)
--	 , MAX(fci.GAAPYldPurchPrc)
--	 , MAX(fci.GAAPYldCurrPrc)
--	 , MAX(fci.InvWalkGAAPYld)
--	 , SUM(fci.AvailCredit)
--	 ,(SUM(fci.AmortCost) / nullif(sUM(fci.EndAdjAmt),0) )
--	 ,  CASE WHEN MAX(i.pricingMethodtype) ='Cost' then MAX(fci.purchPrc) ELSE CASE WHEN  SUM(fci.AvailCredit) <> '0.0' THEN CAST(SUM(fci.PurchPrc * fci.AvailCredit) / SUM(fci.AvailCredit) AS VARCHAR(50)) ELSE '0.0' END END 
--	 , MAX(a.PurchPrc)
--	 , CASE when MAX(i.pricingMethodtype )= 'Cost' THEN MAX(fci.purchPrc) ELSE   COALESCE(sum(fci.MktVal)*100/NULLIF((SUM (fci.AvailCredit)-SUM(fci.AmortCostUnfunded))/100,0), SUM(fci.MktVal)*100/SUM(fci.AvailCredit)+100,100) END
--,null
--,null
--,null
--	 , SUM(fci.AmortCostUnfunded)
--	 , SUM(fci.MktVal)
--	 , SUM(fci.MktVal)
--	 , SUM(fci.UnrealDeprec) + SUM(fci.UnrealApprec)
--	 , CASE WHEN MAX(fci.YesterdayPrice) > '0.01' AND   MAX(fci.TodayPrice) > '0.01' THEN CAST(((MAX(fci.TodayPrice) / NULLIF(MAX(fci.YesterdayPrice),0) ) - 1 ) AS VARCHAR(50)) ELSE 'N/A' END
--	 , SUM(fci.DailyValChg)
--, CASE WHEN MAX(a.YesterdayPrice) > '0.01' AND   MAX(a.TodayPrice) > '0.01' THEN CAST(( ( MAX(a.TodayPrice) / MAX(a.YesterdayPrice) ) - 1 ) AS VARCHAR(50)) ELSE 'N/A' END
--	 , MAX(i.ExtId)
--	 , SUM(fci.UnrealDeprec)
--	 , SUM(fci.UnrealApprec)
--	 , MAX(a.PropReason)
--	 , MAX(a.PropTrack)
--	 , CASE WHEN MAX(i.PricingMethodType) = 'Cost' THEN 'Cost - to be ' + MAX(i.PricingVendor) ELSE MAX(i.PricingVendor) END
--	 , SUM(fci.TotalIntPurchPrc)
--	 , SUM(fci.TotalIntCurrPurchPrc)
--	 , SUM(fci.Cost)
--	 , MAX(fci.FundCreditInstrumentType)
--	 , MAX(iss.IssuerName)
--	 , COUNT(fci.InstrumentId)
--	 , MAX(iss.SubSector)
--	 , MAX(iss.GICSSector)
--	 , MAX(i.ExtId)
--,Null
--,Null
--	 , MAX(p.SortOrder)
--	 , '8'
--	 , NULL
--	 , fci.InstrumentId
--	 , MAX(i.MaturityDate)
--	 , MAX(a.SettDiscPurchPrc)
--	 , SUM(a.PIKPurchAmt)
--	 , SUM(fci.CoupRevAnnualPurch)


-- FROM  [FSFundModel].[Reporting].FundCreditInstrument fci
--	   INNER JOIN [FSFundModel].[Reporting].Asset a
--	   ON    a.FundCreditInstrumentId = fci.FundCreditInstrumentId
--			 -- AND a.InstrumentId = fci.InstrumentId
--	   AND   fci.ExportId = a.ExportId
--	   INNER JOIN [FSFundModel].[Reporting].Instrument i
--	   ON    fci.ExportId     = i.ExportId
--	   AND   fci.InstrumentId = i.InstrumentId
--	   --------------------ADDED-
--	   INNER JOIN [FSFundModel].[Reporting].Issuer iss
--	   ON    fci.ExportId = iss.ExportId
--	   AND   i.IssuerId   = iss.IssuerId
--	   INNER JOIN [FSFundModel].[Reporting].FundIssuer fi
--	   ON    fci.ExportId = fi.ExportId
--	   AND   i.IssuerId   = fi.IssuerId
--	   AND   iss.IssuerId = fi.IssuerId
--	   INNER JOIN  FSFundModel.Reporting.Portfolio p 
--	   ON p.ExportId = fci.ExportId 
--	   AND p.PortfolioId = fci.PortfolioId 
	   
--	   INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id
--	   ON    fci.ExportId = id.ExportId
--	   AND   i.InstDefId  = id.InstDefId
--	   INNER JOIN FSFundModel.Reporting.Fund f ON 
--	   fci.ExportId = f.ExportId 
	   
 
-- WHERE fci.ExportId                       = @ExportID
-- AND   a.FundCreditInstrumentId IS NOT NULL
 
  
-- GROUP BY fci.MktVal
--	 , fci.InstrumentId
--	 , fi.IsQualified
--	 , i.InstrName
--	 , p.SortOrder, id.SortOrder
--   ORDER BY p.SortOrder, id.SortOrder
   
   RETURN
END
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'FUNCTION', N'ValcomBase', NULL, NULL
GO
